	
/**
	AUTHOR		: VEERU
	DATE		: May 30th 2005
	DESCRIPTION	: THIS PROCEDURE IS USED TO INSERT AND UPDATE THE LINE ITEM  DETIALS PARSED FROM THE EXCEL SHEET.
	USAGE           : EXEC SP_SAVE_LINE_ITEM_DETAILS 2, 1282, '3DM-16MM-A008-750905', '','','', 1350,'',10.00,'','CLOSED',1 
*/			

ALTER PROC SP_SAVE_LINE_ITEM_DETAILS


@LINE_ITEM_NO     INT,
@SALESORDER_ID    INT,
@PART_NO      	  VARCHAR(50),
@DESCRIPTION      VARCHAR(500),
@ENTERED_DATE     DATETIME,
@REQUESTED_DATE   DATETIME,
@QUANTITY	  INT,
@SCHEDULE_DATE    DATETIME,
@UNIT_PRICE       FLOAT,
@BOOKING_DATE     DATETIME,
@STATUS           VARCHAR(20),
@SHIPMENT_NO      INT,
@IS_APPROVED      INT    

AS

BEGIN

	DECLARE @DB_BILLING_ADDRESS_ID  INT 
	DECLARE @DB_SHIPPING_ADDRESS_ID INT 
	DECLARE @DB_PRODUCT_ID INT
	DECLARE @DB_SALES_ORDER_ID INT
	DECLARE @DB_LINE_ITEM_ID INT
	DECLARE @DB_PREV_PRODUCT_ID INT

	DECLARE @DB_QTY INT
	DECLARE @DB_PRICE FLOAT
	DECLARE @DB_DATE DATETIME	
	DECLARE @DB_SCHEDULE_DATE DATETIME	

	DECLARE @DB_LINEITEM_DESC VARCHAR(500)
	DECLARE @DB_STATUS VARCHAR(20)
	DECLARE @COMPARE_DESC VARCHAR(10)		
	DECLARE @COMPARE_PRODUCT VARCHAR(10)
	DECLARE @COMPARE_REQ_DATE VARCHAR(10)
	DECLARE @COMPARE_SCHEDULE_DATE VARCHAR(10)
	
	DECLARE @OLD_CHANGE_NUMBER INT
	DECLARE @CHANGEITEM_ID INT
	DECLARE @CHANGE_NUMBER INT
	DECLARE @PG_RELEASE_STATUS VARCHAR(20)	
	DECLARE @F_HOLD_REASON_CODE VARCHAR(10)
	
	IF ( @IS_APPROVED = 1 )
	BEGIN
		SET @F_HOLD_REASON_CODE	= '0'
	END
	ELSE 
	BEGIN
		SET @F_HOLD_REASON_CODE	= ''
	END
	print 'DDDD'+@F_HOLD_REASON_CODE
	SELECT @DB_PRODUCT_ID = PRODUCT_ID FROM PRODUCT WHERE PART_NUMBER = @PART_NO
	SELECT @DB_LINE_ITEM_ID = LINEITEM_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = @SALESORDER_ID AND COOL_LINE_ITEM_NO = @LINE_ITEM_NO AND COOL_SHIPMENT_NO = @SHIPMENT_NO
	IF ( @@ROWCOUNT = 0 )
	BEGIN		
		INSERT INTO CUSTOMER_OPEN_ORDER_LINEITEM( SALESORDER_ID, COOL_LINE_ITEM_NO, PRODUCT_ID, COOL_QTY, COOL_UNIT_COST, COOL_AMOUNT, COOL_REQUESTED_DATE, COOL_BOOKING_DATE, COOL_SCHEDULE_DATE, COOL_STATUS, TIMESTAMP, COOL_DESCRIPTION, COOL_PO_LINE_ITEM_NO, COOL_SHIPMENT_NO ) 
						 VALUES ( @SALESORDER_ID, @LINE_ITEM_NO, @DB_PRODUCT_ID, @QUANTITY, @UNIT_PRICE, ( @QUANTITY * @UNIT_PRICE ), @REQUESTED_DATE, @BOOKING_DATE, @SCHEDULE_DATE, @STATUS, @ENTERED_DATE, @DESCRIPTION, @LINE_ITEM_NO, @SHIPMENT_NO )
		SET @DB_LINE_ITEM_ID = ( SELECT @@IDENTITY )
		
		/*
		* Insert the holds in sales Hold table
		* 
		*/
		INSERT INTO SALES_HOLD_TABLE( LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP ) 
			               VALUES( @DB_LINE_ITEM_ID, @SALESORDER_ID, '', 'OPERATIONS', 'SYSTEM', 0, CURRENT_TIMESTAMP )	 
		
		INSERT INTO SALES_HOLD_TABLE( LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP ) 
				       VALUES( @DB_LINE_ITEM_ID, @SALESORDER_ID, '', 'MARKETING', 'SYSTEM', 0, CURRENT_TIMESTAMP )	 
		
		INSERT INTO SALES_HOLD_TABLE( LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP ) 
				       VALUES( @DB_LINE_ITEM_ID, @SALESORDER_ID, '', 'SALES', 'SYSTEM', 0, CURRENT_TIMESTAMP )
		
		INSERT INTO SALES_HOLD_TABLE( LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP ) 
				       VALUES( @DB_LINE_ITEM_ID, @SALESORDER_ID, @F_HOLD_REASON_CODE, 'FINANCE', 'SYSTEM', @IS_APPROVED, CURRENT_TIMESTAMP ) 

		INSERT INTO CUSTOMER_CHANGE_ORDER_LINEITEM( LINEITEM_ID, SALESORDER_ID, CHANGENO, CCO_LINE_ITEM_NO, PRODUCT_ID, CCO_QTY, CCO_UNIT_COST, CCO_AMOUNT, CCO_REQUESTED_DATE, CCO_BOOKING_DATE, CCO_SCHEDULE_DATE, CCO_STATUS, TIMESTAMP, CCO_PO_LINE_ITEM_NO, CCO_SHIPMENT_NO ) 
 		 			   VALUES ( @DB_LINE_ITEM_ID, @SALESORDER_ID, 0, @LINE_ITEM_NO, @DB_PRODUCT_ID, @QUANTITY, @UNIT_PRICE, ( @QUANTITY * @UNIT_PRICE ), @REQUESTED_DATE, @BOOKING_DATE, @SCHEDULE_DATE, @STATUS, @ENTERED_DATE, @LINE_ITEM_NO, @SHIPMENT_NO  )

	END
	ELSE
	BEGIN
		SELECT @PG_RELEASE_STATUS = STATUS FROM PROGRAM_RELEASE WHERE LINEITEM_ID = @DB_LINE_ITEM_ID
		IF ( @PG_RELEASE_STATUS IS NULL OR @PG_RELEASE_STATUS <> 'SHIPPED' )
		BEGIN
			SELECT @DB_QTY = COOL_QTY, @DB_PREV_PRODUCT_ID = Product_Id, @DB_PRICE = COOL_UNIT_COST, @DB_DATE = COOL_REQUESTED_DATE, @DB_SCHEDULE_DATE = COOL_SCHEDULE_DATE, @DB_LINEITEM_DESC =  COOL_DESCRIPTION, @DB_STATUS = COOL_STATUS
			FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE LINEITEM_ID = @DB_LINE_ITEM_ID 
	
			IF( ( @DB_PREV_PRODUCT_ID <> @DB_PRODUCT_ID ) )
			BEGIN
				SET @COMPARE_PRODUCT ='TRUE'
			END
			ELSE
			BEGIN
				SET @COMPARE_PRODUCT ='FALSE'
			END
	
			IF( ( @DESCRIPTION <> @DB_LINEITEM_DESC ) OR ( @DB_LINEITEM_DESC IS NULL) )
			BEGIN
				SET @COMPARE_DESC ='TRUE'
			END
			ELSE
			BEGIN
				SET @COMPARE_DESC ='FALSE'
			END
			
			IF ( (@DB_DATE <> @REQUESTED_DATE) OR (@DB_DATE IS NULL) )
			BEGIN
				SET @COMPARE_REQ_DATE = 'TRUE'	
			END
			ELSE
			BEGIN
				SET @COMPARE_REQ_DATE = 'FALSE'
			END
	
			IF ( (@DB_SCHEDULE_DATE <> @SCHEDULE_DATE) OR (@DB_SCHEDULE_DATE IS NULL) )
			BEGIN
				SET @COMPARE_SCHEDULE_DATE = 'TRUE'	
			END
			ELSE
			BEGIN
				SET @COMPARE_SCHEDULE_DATE = 'FALSE'
			END
			
			SELECT @OLD_CHANGE_NUMBER = CHANGENO FROM CUSTOMER_CHANGE_ORDER_LINEITEM WHERE SALESORDER_ID = @SALESORDER_ID AND LINEITEM_ID = @DB_LINE_ITEM_ID 
			IF(@OLD_CHANGE_NUMBER IS NULL)
			BEGIN	
			 	INSERT INTO CUSTOMER_CHANGE_ORDER_LINEITEM ( LINEITEM_ID, SALESORDER_ID,CHANGENO, CCO_LINE_ITEM_NO,CCO_PO_LINE_ITEM_NO, PRODUCT_ID, CCO_QTY, CCO_UNIT_COST, CCO_AMOUNT, CCO_REQUESTED_DATE, 
				CCO_BOOKING_DATE,CCO_SUGG_SCHEDULE_DATE_WITH_SUBSTITUTABLES, CCO_SUGG_SCHEDULE_DATE_WITHOUT_SUBSTITUTABLES,CCO_SCHEDULE_DATE, CCO_REVISED_DATE,CCO_TAXABLE, TIMESTAMP, CCO_SHIPMENT_NO )
				( SELECT LINEITEM_ID, SALESORDER_ID, 0 ,COOL_LINE_ITEM_NO,COOL_PO_LINE_ITEM_NO, PRODUCT_ID, COOL_QTY, COOL_UNIT_COST, COOL_AMOUNT, COOL_REQUESTED_DATE, COOL_BOOKING_DATE,
				COOL_SUGG_SCHEDULE_DATE_WITH_SUBSTITUTABLES, COOL_SUGG_SCHEDULE_DATE_WITHOUT_SUBSTITUTABLES,COOL_SCHEDULE_DATE, COOL_REVISED_DATE,COOL_TAXABLE, COOL_BOOKING_DATE, COOL_SHIPMENT_NO 
				FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL WHERE LINEITEM_ID = @DB_LINE_ITEM_ID )	
			END
			
			DECLARE @SALES_CHANGE_NUMBER INT 
			DECLARE @LINEITEM_CHANGE_NUMBER INT
			SELECT @SALES_CHANGE_NUMBER =  ISNULL( MAX(CHANGENO), 0 ) FROM CUSTOMER_CHANGE_ORDER_LINEITEM WHERE LINEITEM_ID = @DB_LINE_ITEM_ID 
			IF( (@DB_QTY <> @QUANTITY) OR (@DB_PRICE <> @UNIT_PRICE) )
			BEGIN
				SET @SALES_CHANGE_NUMBER = @SALES_CHANGE_NUMBER + 1
				
				INSERT INTO CUSTOMER_CHANGE_ORDER_LINEITEM (LINEITEM_ID, SALESORDER_ID,CHANGENO, CCO_LINE_ITEM_NO, CCO_PO_LINE_ITEM_NO, PRODUCT_ID, CCO_QTY, CCO_UNIT_COST, CCO_AMOUNT, CCO_REQUESTED_DATE, 
					CCO_BOOKING_DATE,CCO_SUGG_SCHEDULE_DATE_WITH_SUBSTITUTABLES, CCO_SUGG_SCHEDULE_DATE_WITHOUT_SUBSTITUTABLES,CCO_SCHEDULE_DATE, CCO_REVISED_DATE,CCO_TAXABLE,CCO_STATUS, CCO_SHIPMENT_NO )
				( SELECT LINEITEM_ID, SALESORDER_ID, @SALES_CHANGE_NUMBER ,COOL_LINE_ITEM_NO, COOL_PO_LINE_ITEM_NO, PRODUCT_ID, @QUANTITY, @UNIT_PRICE, @QUANTITY * @UNIT_PRICE, @REQUESTED_DATE, COOL_BOOKING_DATE,
					COOL_SUGG_SCHEDULE_DATE_WITH_SUBSTITUTABLES, COOL_SUGG_SCHEDULE_DATE_WITHOUT_SUBSTITUTABLES,COOL_SCHEDULE_DATE, COOL_REVISED_DATE,COOL_TAXABLE, COOL_STATUS, COOL_SHIPMENT_NO 
					FROM CUSTOMER_OPEN_ORDER_LINEITEM  WHERE LINEITEM_ID = @DB_LINE_ITEM_ID  )
		
				SELECT  @CHANGEITEM_ID = ( SELECT @@IDENTITY )
				SELECT  @CHANGE_NUMBER = ( SELECT MAX( CCO_CHANGE_NUMBER ) FROM CUSTOMER_CHANGE_ORDER_LINEITEM WHERE LINEITEM_ID = @DB_LINE_ITEM_ID  )
		
				IF ( @CHANGE_NUMBER IS NULL )
					SET @CHANGE_NUMBER = 1
				ELSE
					SET @CHANGE_NUMBER = @CHANGE_NUMBER + 1
		
				UPDATE CUSTOMER_CHANGE_ORDER_LINEITEM  SET CCO_CHANGE_NUMBER = @CHANGE_NUMBER, CCO_CHANGE_BY_ID = 'admin', CCO_CHANGE_BY_NAME = null, TIMESTAMP = CURRENT_TIMESTAMP 
					WHERE CHANGEITEM_ID = @CHANGEITEM_ID
				
				UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET COOL_QTY = @QUANTITY, COOL_UNIT_COST = @UNIT_PRICE, COOL_AMOUNT = @QUANTITY * @UNIT_PRICE, COOL_LAST_UPDATED_BY_ID = 'admin',COOL_LAST_UPDATED_BY_NAME = null, TIMESTAMP = CURRENT_TIMESTAMP 
					WHERE LINEITEM_ID = @DB_LINE_ITEM_ID		
				
			END
			
			IF( ( @COMPARE_PRODUCT = 'TRUE' ) OR  ( @COMPARE_REQ_DATE = 'TRUE' ) OR ( @COMPARE_SCHEDULE_DATE = 'TRUE' ) OR ( @COMPARE_DESC = 'TRUE' ) OR @STATUS <> @DB_STATUS )
			BEGIN
				UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET Product_Id = @DB_PRODUCT_ID, COOL_REQUESTED_DATE = @REQUESTED_DATE, COOL_SCHEDULE_DATE = @SCHEDULE_DATE, COOL_LAST_UPDATED_BY_ID = 'admin', COOL_LAST_UPDATED_BY_NAME = null, TIMESTAMP = CURRENT_TIMESTAMP, 
					COOL_DESCRIPTION = @DESCRIPTION, COOL_STATUS = @STATUS, COOL_SHIPMENT_NO = @SHIPMENT_NO WHERE LINEITEM_ID = @DB_LINE_ITEM_ID
				
				UPDATE CUSTOMER_CHANGE_ORDER_LINEITEM SET CCO_REQUESTED_DATE = @REQUESTED_DATE, CCO_SCHEDULE_DATE = @SCHEDULE_DATE, CCO_CHANGE_BY_ID = 'admin', CCO_CHANGE_BY_NAME = null, --TIMESTAMP = CURRENT_TIMESTAMP,
					CCO_STATUS = @STATUS, CCO_SHIPMENT_NO = @SHIPMENT_NO WHERE LINEITEM_ID = @DB_LINE_ITEM_ID AND CHANGENO = @SALES_CHANGE_NUMBER

				UPDATE SALES_HOLD_TABLE SET HOLD_REASON_CODE = @F_HOLD_REASON_CODE, HOLD_BY_USER = 'SYSTEM', IS_APPROVED = @IS_APPROVED, TIMESTAMP = CURRENT_TIMESTAMP 				       
					WHERE LINEITEM_ID = @DB_LINE_ITEM_ID AND SALESORDER_ID = @SALESORDER_ID	AND HOLD_BY_GROUP = 'FINANCE'
				
				-- Update all the previous records in change sales orders also to have new part number
				UPDATE CUSTOMER_CHANGE_ORDER_LINEITEM SET Product_Id = @DB_PRODUCT_ID WHERE LINEITEM_ID = @DB_LINE_ITEM_ID

			END
		END -- IS NOT SHIPPED
	END -- IS OLD LINE ITEM
END

